﻿using Microsoft.Office.Interop.Excel;
using System;
using System.Data;



namespace Yunzuan.Foundation.Excel
{
    public class DataChangeExcel 
    {
        /// <summary>  
        /// 数据库转为excel表格  
        /// </summary>  
        /// <param name="dataTable">数据库数据</param>  
        /// <param name="SaveFile">导出的excel文件</param>  
        public static void DataSetToExcel(System.Data.DataTable dataTable, string SaveFile)
        {
            Application excel;

            _Workbook workBook;

            _Worksheet workSheet;

            object misValue = System.Reflection.Missing.Value;

            excel = new ApplicationClass();

            workBook = excel.Workbooks.Add(misValue);

            workSheet = (_Worksheet)workBook.ActiveSheet;

            int rowIndex = 1;

            int colIndex = 0;

            //取得标题  
            foreach (DataColumn col in dataTable.Columns)
            {
                colIndex++;

                excel.Cells[1, colIndex] = col.ColumnName;
            }

            //取得表格中的数据  
            foreach (DataRow row in dataTable.Rows)
            {
                rowIndex++;

                colIndex = 0;

                foreach (DataColumn col in dataTable.Columns)
                {
                    colIndex++;

                    excel.Cells[rowIndex, colIndex] =

                         row[col.ColumnName].ToString().Trim();

                    //设置表格内容居中对齐  
                    workSheet.get_Range(excel.Cells[rowIndex, colIndex],

                      excel.Cells[rowIndex, colIndex]).HorizontalAlignment =

                      XlVAlign.xlVAlignCenter;
                }
            }

            excel.Visible = false;

            workBook.SaveAs(SaveFile, XlFileFormat.xlWorkbookNormal, misValue,

                misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive,

                misValue, misValue, misValue, misValue, misValue);

            dataTable = null;

            workBook.Close(true, misValue, misValue);

            excel.Quit();

            PublicMethod.Kill(excel);//调用kill当前excel进程  

            releaseObject(workSheet);

            releaseObject(workBook);

            releaseObject(excel);

        }

        private static void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch
            {
                obj = null;
            }
            finally
            {
                GC.Collect();
            }
        }

   
    }
}
